using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.IO;
using System.Web.SessionState;
using NetWing.Model;
using NetWing.Bll;
using Omu.ValueInjecter;
using NetWing.BPM.Core;
using NetWing.BPM.Core.Bll;
using NetWing.Common;
using NetWing.Common.Data;
using NetWing.Common.Data.SqlServer;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using NetWing.Common.Excel;
using NetWing.Common.IO;
using NetWing.Common.IO.DirFile;
using NetWing.BPM.Core.Model;
using NetWing.BPM.Core.Dal;
using NetWing.Common.Data.Filter;

namespace NetWing.BPM.Admin.MJOver.ashx
{
    /// <summary>
    /// 年结转 的摘要说明
    /// </summary>
    public class MJOverHandler : IHttpHandler, IRequiresSessionState
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            int k;
            var json = HttpContext.Current.Request["json"];
            var row = HttpContext.Current.Request["row"];//获得easyui行数据


            var rpm = new RequestParamModel<MJOverModel>(context) { CurrentContext = context };
            if (!string.IsNullOrEmpty(json))
            {
                rpm = JSONhelper.ConvertToObject<RequestParamModel<MJOverModel>>(json);
                rpm.CurrentContext = context;
            }

            switch (rpm.Action)
            {
                case "add":
                    DataTable dt = NetWing.Common.JSON.JSONhelper.jsonToDataTable(row);//获取需要结存的数据
                    if (dt.Rows.Count == 0)//如果没有数据不结存
                    {

                        LogModel log = new LogModel();
                        log.BusinessName = "结存失败";
                        log.OperationIp = PublicMethod.GetClientIP();
                        log.OperationTime = DateTime.Now;
                        log.PrimaryKey = "";
                        log.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                        log.TableName = "";
                        log.note = "结存失败，没有选任何数据。没有对数据库做任何操作！";
                        log.OperationType = (int)OperationType.Other;
                        LogDal.Instance.Insert(log);
                        context.Response.Write("{\"status\":0,\"msg\":\"结存失败，没有提交任何数据！\"}");
                        context.Response.End();

                    }
                    else//有结存数据做结存操作
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr["overmoney"].ToString() == "" || dr["overmoney"].ToString() == "0.00")//结存金额为0 不做任何操作
                            {
                                LogModel log = new LogModel();
                                log.BusinessName = "结存失败";
                                log.OperationIp = PublicMethod.GetClientIP();
                                log.OperationTime = DateTime.Now;
                                log.PrimaryKey = "";
                                log.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                                log.TableName = "";
                                log.note = "结存失败，结存金额为空或结存金额为0。没有对数据库做任何操作！";
                                log.OperationType = (int)OperationType.Other;
                                LogDal.Instance.Insert(log);
                                context.Response.Write("{\"status\":0,\"msg\":\"结存失败，结存金额为0或结存金额为空！\"}");
                                context.Response.End();
                            }
                            else//有结存金额，开始结存操作
                            {
                                SqlTransaction tran = SqlHelper.BeginTransaction(SqlEasy.connString);//取得一个事务
                                try
                                {
                                    string edNumber = common.mjcommon.getedNumber("JC");//得到此次财务订单号，原来是浏览器给，现在从系统获取
                                    int fmainInsertedId = 0;
                                    //先写财务表
                                    #region 财务主表处理
                                    //财务主表处理 方便以后财务统计s
                                    MJFinanceMainModel fmainModel = new MJFinanceMainModel();//初始化财务主表
                                    fmainModel.account = dr["accountName"].ToString();
                                    fmainModel.accountid = int.Parse(dr["KeyId"].ToString());//账号ID
                                    fmainModel.accountb= dr["accountName"].ToString();
                                    fmainModel.accountidb= int.Parse(dr["KeyId"].ToString());
                                    fmainModel.add_time = DateTime.Now;
                                    fmainModel.contact = SysVisitor.Instance.cookiesUserName;
                                    fmainModel.contactid = int.Parse(SysVisitor.Instance.cookiesUserId);
                                    //fmainModel.dep = dep;//部门
                                    fmainModel.depid = int.Parse(SysVisitor.Instance.cookiesUserDepId);//数据权限部门ID
                                    fmainModel.edNumber = edNumber;
                                    fmainModel.ownner = int.Parse(SysVisitor.Instance.cookiesUserId);//数据所有者ID
                                    fmainModel.unit = SysVisitor.Instance.cookiesUserName;//用户
                                    fmainModel.unitid = int.Parse(SysVisitor.Instance.cookiesUserId);//用户ID
                                    fmainModel.note = "[用户备注]"+ dr["usernote"].ToString() + "[系统备注]用户："+ SysVisitor.Instance.cookiesUserName + ",结存账号:"+ dr["accountName"].ToString() + ",金额:"+ dr["overmoney"].ToString() + "";
                                    fmainModel.payment = -decimal.Parse(dr["overmoney"].ToString());
                                    fmainModel.total = -decimal.Parse(dr["overmoney"].ToString());
                                    fmainModel.shifub= -decimal.Parse(dr["overmoney"].ToString());
                                    fmainModel.operateid = int.Parse(SysVisitor.Instance.cookiesUserId);
                                    fmainModel.operate = SysVisitor.Instance.cookiesUserName;
                                    //fmainModel.note = mainInsertedId.ToString();//把插入的ID作为note
                                    fmainInsertedId = DbUtils.tranInsert(fmainModel, tran);//往财务主表插入一条
                                                                                           //财务主表处理 方便以后财务统计e
                                    #endregion


                                    #region 财务明细表处理-结存
                                    //先处理费用科目以便以后更新和系统一致
                                    DataRow mdr = SqlEasy.ExecuteDataRow("select * from MJaccountingSubjects where keyid=79");



                                    MJFinanceDetailModel detailModelmj = new MJFinanceDetailModel();//初始化明细表模型
                                    detailModelmj.edNumber = edNumber;//订单号赋值
                                    detailModelmj.financeId = fmainInsertedId;//父子表关联
                                    detailModelmj.add_time = DateTime.Now;
                                    detailModelmj.up_time = DateTime.Now;
                                    detailModelmj.ownner = int.Parse(SysVisitor.Instance.cookiesUserId);//数据所有者ID
                                    detailModelmj.depid = int.Parse(SysVisitor.Instance.cookiesUserDepId);//数据权限部门ID

                                    detailModelmj.ftype = "支出";
                                    detailModelmj.subject = mdr["subjectName"].ToString();//为了实现名称和数据库统一
                                    detailModelmj.subjectid = int.Parse(mdr["keyid"].ToString());
                                    detailModelmj.num = 1;
                                    detailModelmj.sprice = fmainModel.total;//结存金额
                                    detailModelmj.sumMoney =fmainModel.total;
                                    detailModelmj.stime = DateTime.Now;
                                    detailModelmj.etime = DateTime.Now;
                                    detailModelmj.note = fmainModel.note;
                                    detailModelmj.username = fmainModel.contact;
                                    detailModelmj.mobile = "";
                                    detailModelmj.contact = SysVisitor.Instance.cookiesUserName;
                                    detailModelmj.contactid = int.Parse(SysVisitor.Instance.cookiesUserId);
                                    DbUtils.tranInsert(detailModelmj, tran);//向数据库插入结存明细
                                    #endregion
                                    //再写结存记录
                                    MJOverModel a = new MJOverModel();
                                    a.account = fmainModel.account;
                                    a.accountid = fmainModel.accountid;
                                    a.overmoney = decimal.Parse(dr["overmoney"].ToString());
                                    a.add_time = DateTime.Now;
                                    a.up_time = DateTime.Now;
                                    a.depid= int.Parse(SysVisitor.Instance.cookiesUserDepId);
                                    a.ownner= int.Parse(SysVisitor.Instance.cookiesUserId);
                                    a.edNumber = edNumber;
                                    a.operate= SysVisitor.Instance.cookiesUserName;
                                    a.operateid= int.Parse(SysVisitor.Instance.cookiesUserId);
                                    a.note = fmainModel.note;
                                    DbUtils.tranInsert(a,tran);

                                    //最后更新银行账户余额

                                    //写结存成功日志
                                    LogModel log = new LogModel();
                                    log.BusinessName = "结存操作成功";
                                    log.OperationIp = PublicMethod.GetClientIP();
                                    log.OperationTime = DateTime.Now;
                                    log.PrimaryKey = "";
                                    log.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                                    log.TableName = "";
                                    log.note = "结存操作成功！";
                                    log.OperationType = (int)OperationType.Other;
                                    LogDal.Instance.Insert(log);
                                    //
                                    tran.Commit();//提交事务

                                    //更新银行余额 因为这里账户id都是唯一的 这里不考虑数据权限
                                    SqlDataReader dra = SqlEasy.ExecuteDataReader("select * from MJBankAccount");
                                    while (dra.Read())
                                    {
                                        string sumsqlb = "select isnull(sum(shifub),0) shifub  from MJFinanceMain where accountidb=" + dra["Keyid"].ToString() + "";
                                        string sumsqlc = "select isnull(sum(shifuc),0) shifuc  from MJFinanceMain where accountidc=" + dra["Keyid"].ToString() + "";
                                        string sumsqld = "select isnull(sum(shifud),0) shifud  from MJFinanceMain where accountidd=" + dra["Keyid"].ToString() + "";
                                        decimal moneyb = (decimal)SqlEasy.ExecuteScalar(sumsqlb);
                                        decimal moneyc = (decimal)SqlEasy.ExecuteScalar(sumsqlc);
                                        decimal moneyd = (decimal)SqlEasy.ExecuteScalar(sumsqld);
                                        SqlEasy.ExecuteNonQuery("update MJBankAccount set accountBalance=" + (moneyb + moneyc + moneyd) + "  where keyid=" + dra["keyid"].ToString() + "");

                                        LogModel loga = new LogModel();
                                        loga.BusinessName = "统计银行余额-从结存入口";
                                        loga.OperationIp = PublicMethod.GetClientIP();
                                        loga.OperationTime = DateTime.Now;
                                        loga.PrimaryKey = "";
                                        loga.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                                        loga.TableName = "";
                                        loga.note = "账户名:" + dra["accountName"].ToString() + "depid:" + dra["depid"].ToString() + "账户ID：" + dra["keyid"].ToString() + "更新余额：" + (moneyb + moneyc + moneyd);
                                        loga.OperationType = (int)OperationType.Other;
                                        LogDal.Instance.Insert(loga);
                                    }


                                    

                                }
                                catch (Exception e)
                                {
                                    LogModel log = new LogModel();
                                    log.BusinessName = "结存失败";
                                    log.OperationIp = PublicMethod.GetClientIP();
                                    log.OperationTime = DateTime.Now;
                                    log.PrimaryKey = "";
                                    log.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                                    log.TableName = "";
                                    log.note = "结存失败！"+e.Message+"";
                                    log.OperationType = (int)OperationType.Other;
                                    LogDal.Instance.Insert(log);
                                    tran.Rollback();//失败回滚事务
                                    context.Response.Write("{\"status\":0,\"msg\":\"结存失败！"+e.Message+"\"}");
                                    context.Response.End();
                                }
                            }
                        }
                        context.Response.Write("{\"status\":1,\"msg\":\"结存成功！\"}");
                        context.Response.End();

                    }

                    //context.Response.Write(MJOverBll.Instance.Add(rpm.Entity));
                    break;
                case "edit":
                    MJOverModel d = new MJOverModel();
                    d.InjectFrom(rpm.Entity);
                    d.KeyId = rpm.KeyId;
                    context.Response.Write(MJOverBll.Instance.Update(d));
                    break;
                case "export":
                    //string fields = rpm.fields;
                    string fields = rpm.CurrentContext.Request["fields"];
                    string tablename = TableConvention.Resolve(typeof(MJOverModel));//得到表名
                    DataTable xlsDt = SqlEasy.ExecuteDataTable("select " + fields + " from " + tablename + "");
                    ExcelHelper.NPIOtoExcel(xlsDt, HttpContext.Current.Server.MapPath("\\upload\\excel\\" + tablename + ".xls"));
                    context.Response.Write("{\"status\":\"ok\",\"filename\":\"" + tablename + ".xls\"}");
                    break;
                case "inport"://从Excel导入到数据库
                    if (context.Request["REQUEST_METHOD"] == "OPTIONS")
                    {
                        context.Response.End();
                    }
                    SaveFile("~/temp/", context);
                    break;
                case "delete":
                    context.Response.Write(MJOverBll.Instance.Delete(rpm.KeyId));
                    break;
                case "alldel"://2017-04-05新增的功能 批量删除删除结果返回删除条数
                    context.Response.Write(NetWing.Dal.MJOverDal.Instance.Delete(rpm.KeyIds));
                    break;
                default:
                    string sqlwhere = "(depid in (" + SysVisitor.Instance.Departments + "))";
                    if (SysVisitor.Instance.IsAdmin)
                    { //判断是否是超管如果是超管理，所有显示
                        sqlwhere = " 1=1 ";//如果是超管则不显示
                    }
                    if (!string.IsNullOrEmpty(rpm.Filter))//如果筛选不为空
                    {
                        string str = " and " + FilterTranslator.ToSql(rpm.Filter);
                        sqlwhere = sqlwhere + str;
                    }

                    string sort = rpm.Sort;
                    if (sort == null)
                    {
                        sort = "keyid desc";
                    }


                    //TableConvention.Resolve(typeof(MJUserModel)) 转换成表名
                    var pcp = new ProcCustomPage(TableConvention.Resolve(typeof(MJOverModel)))
                    {
                        PageIndex = rpm.Pageindex,
                        PageSize = rpm.Pagesize,
                        OrderFields = sort,
                        WhereString = sqlwhere

                    };
                    int recordCount;
                    DataTable mydt = DbUtils.GetPageWithSp(pcp, out recordCount);
                    context.Response.Write(JSONhelper.FormatJSONForEasyuiDataGrid(recordCount, mydt));
                    //context.Response.Write(MJOverBll.Instance.GetJson(rpm.Pageindex, rpm.Pagesize, rpm.Filter, rpm.Sort, rpm.Order));
                    break;
            }
        }

        /// <summary>
        /// 文件保存操作
        /// </summary>
        /// <param name="basePath"></param>
        private void SaveFile(string basePath, HttpContext context)
        {
            var name = string.Empty;
            basePath = (basePath.IndexOf("~") > -1) ? context.Server.MapPath(basePath) :
            basePath;
            HttpFileCollection files = context.Request.Files;

            if (!Directory.Exists(basePath))//如果文件夹不存在创建文件夹
                Directory.CreateDirectory(basePath);
            //清空temp文件夹
            DirFileHelper.ClearDirectory(basePath);

            var suffix = files[0].ContentType.Split('/');
            var _suffix = suffix[1].Equals("jpeg", StringComparison.CurrentCultureIgnoreCase) ? "" : suffix[1];
            var _temp = System.Web.HttpContext.Current.Request["name"];

            if (!string.IsNullOrEmpty(_temp))
            {
                name = _temp;
            }
            else
            {
                Random rand = new Random(24 * (int)DateTime.Now.Ticks);
                name = rand.Next() + "." + _suffix;
            }

            var full = basePath + name;
            files[0].SaveAs(full);

            DataTable dt = NPOIHelper.ImportExceltoDt(full);
            string connectionString = SqlEasy.connString;
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
            sqlbulkcopy.DestinationTableName = TableConvention.Resolve(typeof(MJOverModel));//数据库中的表名
                                                                                            //自定义的datatable和数据库的字段进行对应  
                                                                                            //sqlBC.ColumnMappings.Add("id", "tel");  
                                                                                            //sqlBC.ColumnMappings.Add("name", "neirong");  
            int k = dt.Rows.Count - 1;                                                                       //注意一个问题，最后一列是字段数
            for (int i = 0; i < (dt.Columns.Count - 1); i++)
            {
                sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName.ToString(), dt.Columns[i].ColumnName.ToString());

            }
            var _result = "";
            try
            {
                sqlbulkcopy.WriteToServer(dt);
                _result = "{\"msg\" : \"导入数据库成功!\", \"result\" : " + k + ", \"filename\" : \"" + name + "\"}";
            }
            catch (Exception)
            {
                _result = "{\"msg\" : \"导入失败,可能模板不对，或其他原因，建议导出数据作为模板重新处理。注意导入没有校验数据重复功能。请人工校验数据!\", \"result\" : 0, \"filename\" : \"" + name + "\"}";
                //throw;
            }



            System.Web.HttpContext.Current.Response.Write(_result);

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}